﻿CREATE FUNCTION dbo.GetPageNumbers
(	@LoanID int = 3261,
	@ImageTypeID int = 1,
	@PageNumbers varchar(1024) = '' -- '1,2,5,34,...' ('' - to get whole folder)
)RETURNS @PAGE_NUMS TABLE(Pos int IDENTITY,PageNumber int,PageID int) AS BEGIN

IF @PageNumbers = '' SET @PageNumbers = '-1'

DECLARE @PNs TABLE(Pos int IDENTITY,PageNumber int)

INSERT INTO @PNs SELECT Element FROM master.dbo.Split(@PageNumbers,',')

IF (SELECT COUNT(*) FROM @PNs) = 1 AND (SELECT TOP 1 PageNumber FROM @PNs) < 0
	INSERT INTO @PNs(PageNumber)
	SELECT t_Page.PageNumber FROM t_Image INNER JOIN t_Page ON t_Image.ID = t_Page.ImageID
	WHERE t_Image.LoanID = @LoanID AND t_Image.ImageTypeID = @ImageTypeID AND
				PageNumber >= (SELECT -PageNumber FROM @PNs)
	ORDER BY PageNumber

INSERT INTO @PAGE_NUMS(PageNumber,PageID)
SELECT PNs.PageNumber,t_Page.ID FROM t_Page
INNER JOIN  @PNs PNs ON PNs.PageNumber = t_Page.PageNumber
INNER JOIN t_Image ON t_Image.ID = t_Page.ImageID
WHERE (t_Image.LoanID = @LoanID) AND (t_Image.ImageTypeID = @ImageTypeID)
ORDER BY PNs.Pos

RETURN

END

